﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;

namespace System.Modules.SQL.Extensions
{
    /// <summary>
    /// 
    /// </summary>
    public static class SqlDatabaseExtensions
    {
        #region Public Methods
        #region Enumerate Stored Procedure Parameters
        /// <summary>
        /// This Method Loads all stored procedure's parameters
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="storedProcedureName"></param>
        /// <param name="schema">[dbo]</param>
        /// <returns></returns>
        public static List<String> EnumerateStoredProcedureParameters(this SqlDatabase provider, string storedProcedureName, string schema)
        {
            var datasource = new List<string>();
            var command = new SqlCommand();
            command.GetStoredProcedureParametersCommand(storedProcedureName, schema);
            using (var reader = provider.ExecuteReader(command))
            {
                if (reader != null)
                {
                    while (reader.Read())
                    {
                        datasource.Add(reader["name"].ToString().Replace("@", String.Empty));
                    }
                }
            }
            return datasource;
        }
        #endregion

        #region Execute
        /// <summary>
        /// This method executes non query command.
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public static bool Execute(this SqlDatabase provider, SqlCommand command)
        {
            return provider.ExecuteNonQuery(command) > 0;
        }
        #endregion

        #region ExecuteScalar
        /// <summary>
        /// This method executes non query command.
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="command"></param>
        /// <returns></returns>
        public static T ExecuteScalar<T>(this SqlDatabase provider, SqlCommand command) where T : new()
        {
            var scalar = default(T);
            var value = provider.ExecuteScalar(command);
            if (value != null)
            {
                try
                {
                    scalar = (T)value;
                }
                catch (Exception)
                {
                    scalar = default(T);
                }
            }
            return scalar;
        }
        #endregion

        #region Get Multiple
        /// <summary>
        /// This method gets multiple values of defined type.
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="command"></param>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="TBaseObject"> </typeparam>
        /// <returns></returns>
        public static List<T> GetMultiple<TBaseObject, T>(this SqlDatabase provider, SqlCommand command) where T : new()
        {
            var dataSource = new List<T>();
            var ds = provider.ExecuteDataSet(command);
            if (ds != null && ds.Tables.Count > 0)
            {
                dataSource = ds.Tables[0].Load<TBaseObject, T>();
            }
            return dataSource;
        }
        #endregion

        #region Get
        /// <summary>
        /// This method gets single value of defined type
        /// </summary>
        /// <param name="provider"></param>
        /// <param name="command"></param>
        /// <typeparam name="T"></typeparam>
        /// <typeparam name="TBaseObject"> </typeparam>
        /// <returns></returns>
        public static T Get<TBaseObject, T>(this SqlDatabase provider, SqlCommand command) where T : new()
        {
            return provider.GetMultiple<TBaseObject, T>(command).FirstOrDefault();
        }
        #endregion
        #endregion
    }
}
